package com.bookyourtrek.model;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import com.bookyourtrek.util.DbConnection;
import com.bookyourtrek.util.RuntimeSettings;
import com.mysql.jdbc.Statement;
import org.apache.commons.lang3.StringEscapeUtils;

public class TrekModel {

	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private String sqlQuery = "";
	private Connection conn = null;
	/**
	 * function to insert data into trekking table 
	 * @author ananda
	 * @param trektitle
	 * @param location
	 * @param locationdetail
	 * @param date
	 * @param gender
	 * @param duration
	 * @param distance
	 * @param difficultylevel
	 * @param trekplan
	 * @param gears
	 * @param organizationname
	 * @param organizationphone
	 * @return 
	 * @throws ParseException 
	 */
	public BigInteger inserttrek(String trektitle, String location,
			String locationdetail, String date, String gender, String duration,
			String distance, String difficultylevel, String trekplan,
			List<String> gears, String organizationname,
			String organizationphone, BigInteger userid, String status) throws ParseException {
		
		BigInteger trekid = null;
		
		sqlQuery = "INSERT INTO `bookyourtrek`.`trekking`"
				+ " (`trektitle`, `location`, `locationdetail`, `date`, `gender`," +
				" `duration`, `distance`, `difficultylevel`, `trekplan`, `gears`, " +
				"`organizationname`, `organizationphone`, `userid`, `status`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, trektitle);
			ps.setString(2, location);
			ps.setString(3, locationdetail);
			//DateTimeFormatter formatter = DateTimeFormat.forPattern("dd-mm-yyyy");
      		//DateTime newdatefrmt = formatter.parseDateTime(date);
			SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yy");
			System.out.println(sdf.parse(date).getTime());
			java.sql.Date fdate = new java.sql.Date(sdf.parse(date).getTime());
			ps.setDate(4, fdate);
			ps.setString(5, gender);
			ps.setString(6, duration);
			ps.setString(7, distance);
			ps.setString(8, difficultylevel);
			ps.setString(9, StringEscapeUtils.escapeHtml4(trekplan));
			ps.setString(10, StringUtils.join(gears, ','));
			ps.setString(11, organizationname);
			ps.setString(12, organizationphone);
			ps.setLong(13, userid.longValue());
			ps.setString(14, status);
			ps.executeUpdate();
			//get id of inserted record
			ResultSet keys = ps.getGeneratedKeys();    
			keys.next();  
			trekid = BigInteger.valueOf(keys.getLong(1));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return trekid;
	}
	
	/**
	 * function to get data from trekking table
	 * @author ananda
	 * @param trekid
	 * @return
	 */
	public ArrayList<TrekPojo> gettrekdetails(BigInteger trekid) {
		ArrayList<TrekPojo> trekpojo = new ArrayList<TrekPojo>();
		sqlQuery = "SELECT trekkingid, trektitle, locationdetail, location," +
				" date, gender, duration, distance, difficultylevel," +
				" trekplan, gears, organizationname, organizationphone," +
				" userid, status FROM bookyourtrek.trekking"
				+ " where trekkingid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, trekid.longValue());
			rs = ps.executeQuery();
			while (rs.next()) {
				TrekPojo tp = new TrekPojo();
				tp.setTrekkingid(BigInteger.valueOf(rs.getLong(1)));
				tp.setTrektitle(rs.getString(2));
				tp.setLocationdetail(rs.getString(3));
				tp.setLocation(rs.getString(4));
				SimpleDateFormat toFormat = new SimpleDateFormat("dd-MM-yy");
				tp.setDate((rs.getDate(5).toString()));
				tp.setGender(rs.getString(6));
				tp.setDuration(rs.getString(7));
				tp.setDistance(rs.getString(8));
				tp.setDifficultylevel(rs.getString(9));
				//System.out.println(rs.getString(10));
				tp.setTrekplan(StringEscapeUtils.unescapeHtml4(rs.getString(10)));
				tp.setGears(rs.getString(11));
				tp.setOrganizationname(rs.getString(12));
				tp.setOrganizationphone(rs.getString(13));
				tp.setUserid(BigInteger.valueOf(rs.getLong(14)));
				tp.setStatus(rs.getString(15));
				trekpojo.add(tp);
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		return trekpojo;
	}

	/**
	 * function to activate trek
	 * @author ananda
	 * @param trekid
	 */
	public void activatetrek(BigInteger trekid) {
		sqlQuery = "UPDATE bookyourtrek.trekking SET status = ? WHERE trekkingid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setInt(1, RuntimeSettings.ADVENTURESTATUSPENDING);
			ps.setLong(2, trekid.longValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	/**
	 * function to get total no. of trek created
	 * @author ananda
	 * @return total no of treks
	 * @throws SQLException
	 */
	public BigInteger gettotaltrek() throws SQLException {
		BigInteger totaltrek = null;
		sqlQuery = "SELECT COUNT(trekkingid) FROM bookyourtrek.trekking";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			rs = ps.executeQuery();
			while (rs.next()) {
				totaltrek = BigInteger.valueOf(rs.getInt(1));
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		return totaltrek;
	}

	/**
	 * function to update trek table
	 * @author ananda
	 * @param trektitle
	 * @param location
	 * @param locationdetail
	 * @param date
	 * @param gender
	 * @param duration
	 * @param distance
	 * @param difficultylevel
	 * @param trekplan
	 * @param gears
	 * @param organizationname
	 * @param organizationphone
	 * @param userid
	 * @param string
	 * @param trekkingid
	 * @throws ParseException 
	 */
	public void updatetrek(String trektitle, String location,
			String locationdetail, String date, String gender, String duration,
			String distance, String difficultylevel, String trekplan,
			List<String> gears, String organizationname,
			String organizationphone, BigInteger userid, String status,
			BigInteger trekkingid) throws ParseException {
		
		sqlQuery = "UPDATE bookyourtrek.trekking SET trektitle = ?, location = ?," +
				" locationdetail = ?, date = ?, gender = ?, duration = ?, distance = ?," +
				" difficultylevel = ?, trekplan = ?, gears = ?, organizationname = ?," +
				" organizationphone = ?, userid = ?, status = ? WHERE trekkingid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, trektitle);
			ps.setString(2, location);
			ps.setString(3, locationdetail);
			//DateTimeFormatter formatter = DateTimeFormat.forPattern("dd-mm-yyyy");
			//DateTime newdatefrmt = formatter.parseDateTime(date);
			SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yy");
			java.sql.Date fdate = new java.sql.Date(sdf.parse(date).getTime());
			ps.setDate(4, fdate);
			ps.setString(5, gender);
			ps.setString(6, duration);
			ps.setString(7, distance);
			ps.setString(8, difficultylevel);
			ps.setString(9, StringEscapeUtils.escapeHtml4(trekplan));
			ps.setString(10, StringUtils.join(gears, ','));
			ps.setString(11, organizationname);
			ps.setString(12, organizationphone);
			ps.setLong(13, userid.longValue());
			ps.setString(14, status);
			ps.setLong(15, trekkingid.longValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	public void maketrekactive(BigInteger adventureid) {
		sqlQuery = "UPDATE bookyourtrek.trekking SET status = ? WHERE trekkingid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setInt(1, RuntimeSettings.ADVENTURESTATUSACTIVE);
			ps.setLong(2, adventureid.longValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void canceltrek(BigInteger adventureid) {
		sqlQuery = "UPDATE bookyourtrek.trekking SET status = ? WHERE trekkingid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setInt(1, RuntimeSettings.ADVENTURESTATUSINACTIVE);
			ps.setLong(2, adventureid.longValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
